#include "sqlmain.h"
#include <QSqlDatabase>
#include <QDateTime>
#include <QSqlQuery>
#include <QDebug>
#include <QJsonObject>
#include <QJsonDocument>
#include <QJsonArray>
#include <QDateTime>
#include <QSqlError>
SqlMain *SqlMain::obj = nullptr;

SqlMain::SqlMain(QObject *parent)
    : QObject{parent}
{
    db = new QSqlDatabase(
                QSqlDatabase::addDatabase("QMYSQL"));
    db->setHostName("www.zuozl1992.com");
    db->setPort(20101);
    db->setUserName("IotTeam0");
    db->setPassword("briup123");
    db->setDatabaseName("IotTeam0");
    qDebug() << "Sql connect:" << db->open();
}

SqlMain *SqlMain::getObject()
{
    if(obj == nullptr)
        obj = new SqlMain;
    return obj;
}

void SqlMain::insertValue(int type, int index, int value)
{
    QString tableName = QString("t_device_%1_%2_value")
                        .arg(type)
                        .arg(index);
    QString cmd = QString("INSERT INTO %1 (value, time) VALUES (%2, '%3');")
                    .arg(tableName)
                    .arg(value)
                    .arg(QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss"));
    QSqlQuery quary(*db);
    quary.exec(cmd);
    return;
}

void SqlMain::insertValue(int type, int index, double value)
{
    QString tableName = QString("t_device_%1_%2_value")
                        .arg(type)
                        .arg(index);
    QString cmd = QString("INSERT INTO %1 (value, time) VALUES (%2, '%3');")
                    .arg(tableName)
                    .arg(value)
                    .arg(QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss"));
    QSqlQuery quary(*db);
    quary.exec(cmd);
    return;
}

void SqlMain::insertValue(int type, int index, bool value)
{
    QString tableName = QString("t_device_%1_%2_value")
                        .arg(type)
                        .arg(index);
    QString cmd = QString("INSERT INTO %1 (value, time) VALUES (%2, '%3');")
                    .arg(tableName)
                    .arg(value ? 1 : 0)
                    .arg(QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss"));
    QSqlQuery quary(*db);
    quary.exec(cmd);
    return;
}

void SqlMain::insertValue(int type, int index, QString value)
{
    QString tableName = QString("t_device_%1_%2_value")
                        .arg(type)
                        .arg(index);
    QString cmd = QString("INSERT INTO %1 (value, time) VALUES ('%2', '%3');")
                    .arg(tableName)
                    .arg(value)
                    .arg(QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss"));
    QSqlQuery quary(*db);
    quary.exec(cmd);
    return;
}

void SqlMain::updateDeviceTime(int type, int index)
{
    QString cmd = QString("UPDATE t_device SET last_upload_time='%1' "
                          "WHERE device_type_id=%2 AND "
                          "device_type_index=%3;")
                    .arg(QDateTime::currentDateTime().toString("yyyy-MM-dd hh:mm:ss"))
                    .arg(type).arg(index);
    QSqlQuery quary(*db);
    quary.exec(cmd);
}

bool SqlMain::userLogin(QString username, QString password)
{
    QString cmd = QString("SELECT password FROM t_user WHERE username='%1';")
                    .arg(username);
    QSqlQuery query(*db);
    bool ok = query.exec(cmd);
    if(!ok)//执行失败
        return false;
    if(!query.next())//查不到用户
        return false;
    QString sqlPassword = query.value(0).toString();
    if(password != sqlPassword)//密码不对
        return false;
    return true;
}

bool SqlMain::userRegister(QString username, QString password)
{
    QString cmd = QString("INSERT INTO t_user (username, password) VALUES('%1', '%2');")
                    .arg(username)
                    .arg(password);
    QSqlQuery query(*db);
    bool ok = query.exec(cmd);
    if(!ok)
        return false;
    int n = query.numRowsAffected();
    if(n != 1)
        return false;
    return true;
}

int SqlMain::getIntValue(int deviceTypeID, int deviceTypeIndex)
{
    QString cmd = QString("SELECT value FROM t_device_%1_%2_value WHERE "
                          "id = (SELECT MAX(id) FROM t_device_%1_%2_value);")
                    .arg(deviceTypeID)
                    .arg(deviceTypeIndex);
    QSqlQuery query(*db);
    bool ok = query.exec(cmd);
    if(!ok)
        return 0;
    if(!query.next())
        return 0;
    int re = query.value(0).toInt();
    return re;
}

double SqlMain::getDoubleValue(int deviceTypeID, int deviceTypeIndex)
{
    QString cmd = QString("SELECT value FROM t_device_%1_%2_value WHERE "
                          "id = (SELECT MAX(id) FROM t_device_%1_%2_value);")
                    .arg(deviceTypeID)
                    .arg(deviceTypeIndex);
    QSqlQuery query(*db);
    bool ok = query.exec(cmd);
    if(!ok)
        return 0;
    if(!query.next())
        return 0;
    double re = query.value(0).toDouble();
    return re;
}
bool SqlMain::getBoolValue(int deviceTypeID, int deviceTypeIndex)
{
    QString cmd = QString("SELECT value FROM t_device_%1_%2_value WHERE "
                          "id = (SELECT MAX(id) FROM t_device_%1_%2_value);")
                    .arg(deviceTypeID)
                    .arg(deviceTypeIndex);
    QSqlQuery query(*db);
    bool ok = query.exec(cmd);
    if(!ok)
        return false;
    if(!query.next())
        return false;
    int re = query.value(0).toInt();
    return re == 1 ? true : false;
}

QString SqlMain::getStringValue(int deviceTypeID, int deviceTypeIndex)
{
    QString cmd = QString("SELECT value FROM t_device_%1_%2_value WHERE "
                          "id = (SELECT MAX(id) FROM t_device_%1_%2_value);")
                    .arg(deviceTypeID)
                    .arg(deviceTypeIndex);
    QSqlQuery query(*db);
    bool ok = query.exec(cmd);
    if(!ok)
        return QString();
    if(!query.next())
        return QString();
    QString re = query.value(0).toString();
    return re;
}

QJsonArray SqlMain::getDeviceList()
{
    QString cmd = QString("SELECT * FROM t_device;");
    QSqlQuery query(*db);
    bool ok = query.exec(cmd);
    if(!ok)
        return QJsonArray();
    QJsonArray arr;
    while(query.next())
    {
        QString name = query.value(1).toString();
        int dti = query.value(2).toInt();
        int dtn = query.value(3).toInt();
        QString unit = query.value(4).toString();
        QString remarks = query.value(5).toString();
        QString lut = query.value(6).toDateTime().toString("yyyy-MM-dd hh:mm:ss");
        QJsonObject obj;
        obj.insert("name", name);

        obj.insert("device_type_id", dti);
        obj.insert("device_type_index", dtn);
        obj.insert("unit", unit);
        obj.insert("remarks", remarks);
        obj.insert("last_upload_time", lut);
        arr.append(obj);
    }
    return arr;
}

QJsonArray SqlMain::getDeviceHistory(int deviceTypeId, int deviceTypeIndex, int mode)
{
    int day;
    if(mode == 0)
        day = 1;
    else
        day = 7;
    QString cmd = QString("SELECT * FROM t_device_%1_%2_value WHERE time > subdate(now(), %3) ORDER BY time;")
                .arg(deviceTypeId)
                .arg(deviceTypeIndex)
                .arg(day);
    QSqlQuery query(*db);
    bool ok = query.exec(cmd);
    if(!ok)
        return QJsonArray();
    QJsonArray arr;
    while(query.next())
    {
        int id = query.value(0).toInt();
        QString time = query.value(2).toDateTime().toString("yyyy-MM-dd hh:mm:ss");
        QJsonObject obj;
        if(deviceTypeId >= 101 && deviceTypeId <= 109)
        {
            double value = query.value(1).toDouble();
            obj.insert("value", value);
        }
        else if(deviceTypeId >= 201 && deviceTypeId < 208)
        {
            bool value = query.value(1).toInt() == 1 ? true : false;
            obj.insert("value", value);
        }
        else
        {
            QString value = query.value(1).toString();
            obj.insert("value", value);
        }
        obj.insert("id", id);
        obj.insert("time", time);
        arr.append(obj);
    }
    return arr;
}

QJsonArray SqlMain::getDeviceType()
{
    QString cmd = QString("SELECT * FROM t_device_type;");
    QSqlQuery query(*db);
    bool ok = query.exec(cmd);
    if(!ok)
        return QJsonArray();
    QJsonArray arr;
    while(query.next())
    {
        int id = query.value(0).toInt();
        QString name = query.value(1).toString();
        QJsonObject obj;
        obj.insert("id", id);
        obj.insert("name", name);
        arr.append(obj);
    }
    return arr;
}

bool SqlMain::addDevice(QString name, int deviceTypeId, int deviceTypeIndex, QString unit, QString remarks)
{
    QString cmd = QString("INSERT INTO t_device (name, device_type_id, device_type_index, unit, remarks) "
                          "VALUES('%1', %2, %3, '%4', '%5');")
                    .arg(name).arg(deviceTypeId)
                    .arg(deviceTypeIndex).arg(unit).arg(remarks);
    QSqlQuery query(*db);
    bool ok = query.exec(cmd);
    if(!ok)
        return false;
    if(query.numRowsAffected() != 1)
        return false;
    QString valueType;
    if(deviceTypeId >= 101 && deviceTypeId <= 104)
        valueType = "DOUBLE";
    else if(deviceTypeId >= 105 && deviceTypeId <= 109)
        valueType = "INT";
    else if(deviceTypeId >= 201 && deviceTypeId <= 208)
        valueType = "TINYINT";
    else
        valueType = "VARCHAR(255)";
    QString cmd2 = QString("CREATE TABLE IF NOT EXISTS t_device_%1_%2_value("
                           "    id INT NOT NULL AUTO_INCREMENT,"
                           "    value %3 NOT NULL,"
                           "    time datetime NOT NULL,"
                           "    PRIMARY KEY(id)"
                           ");")
                    .arg(deviceTypeId)
                    .arg(deviceTypeIndex)
                    .arg(valueType);
    ok = query.exec(cmd2);
    if(!ok)
        return false;
    return true;
}

bool SqlMain::delDevice(int deviceTypeId, int deviceTypeIndex)
{
    QString cmd = QString("DROP TABLE t_device_%1_%2_value;").arg(deviceTypeId).arg(deviceTypeIndex);
    QSqlQuery query(*db);
    bool ok = query.exec(cmd);
    if(!ok)
        return false;
    QString cmd2 = QString("DELETE FROM t_device WHERE device_type_id=%1 AND device_type_index=%2;")
                .arg(deviceTypeId).arg(deviceTypeIndex);
    ok = query.exec(cmd2);
    if(!ok)
        return false;
    return true;
}






